﻿/*
 * Created by SharpDevelop.
 * User: Dhanesh
 * Date: 16-10-2008
 * Time: 00:16:31
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace ChallanSystem
{
	/// <summary>
	/// Description of Order.
	/// </summary>
	public class Order
	{
		
		private static string strCon = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=./Data/DB.mdb" ;
		private OleDbConnection myCon = new OleDbConnection(strCon);
		
		int customerid;
		
		public int CustomerID {
			get { return customerid; }
			set { customerid = value; }
		}
		
		int transporterid;
		
		public int TransporterID {
			get { return transporterid; }
			set { transporterid = value; }
		}
		
		int lrno;
		
		public int LRNo {
			get { return lrno; }
			set { lrno = value; }
		}
		
		DataSet dsProd = null;
		
		public DataSet Products {
			get { return dsProd; }
			set { dsProd = value; }
		}
		
		public Order()
		{
		}
		
		#region View()
		public DataSet View()
		{
			string sqlStr = "SELECT * FROM Products";
			//Make a DataSet object
			DataSet myDataSet = new DataSet();
			//Using the OleDbDataAdapter execute the query
			OleDbDataAdapter myAdapter = new OleDbDataAdapter( sqlStr , myCon );
			//Fill the DataSet with the Table 'Product'
			myAdapter.Fill(myDataSet,"Products");
			return myDataSet;
		}
		#endregion

		#region Add()
		public int Add()
		{
			
			int done = 0;
			
			if( customerid != null && transporterid != null && lrno != null && dsProd != null)
			{
				//the string to get values from the textboxes and form an "INSERT INTO"
				// statement.
				int orderAmount = 0;
				foreach (DataRow drOrderAmt in dsProd.Tables[0].Rows)
				{
					//MessageBox.Show("hello :");
					orderAmount += Convert.ToInt32(drOrderAmt[4].ToString());
					//MessageBox.Show("Amount : "+orderAmount);
				}
				
				
				string sqlStr = "INSERT INTO Orders(Customer_ID,Transporter_ID,LR_No, Total_Amount) Values(";
				sqlStr += Convert.ToString(customerid) + ",";
				sqlStr += Convert.ToString(transporterid) + ",";
				sqlStr += Convert.ToString(lrno)+",";
				sqlStr += Convert.ToString(orderAmount)+")";
				OleDbCommand myCmd = new OleDbCommand( sqlStr , myCon ) ;
				try
				{
					myCon.Open();
					//Execute the statement
					myCmd.ExecuteNonQuery();
					//statusBar.Text="Connected - Now you can add records";
					
					string sqlStrOrderID = "Select max(order_id) as Order_ID from Orders ";
					DataSet orderIDDS = new DataSet();
					//Using the OleDbDataAdapter execute the query
					try
					{
						OleDbDataAdapter myAdapterOrderID = new OleDbDataAdapter( sqlStrOrderID , myCon );
						//Fill the DataSet with the Table 'Product'
						myAdapterOrderID.Fill(orderIDDS,"Orders");
					}
					catch(Exception e)
					{
						MessageBox.Show("Your request Cannot be Completed :- "+e.ToString());
					}
					int maxOrderID = Int32.Parse(orderIDDS.Tables[0].Rows[0].ItemArray[0].ToString());
					
					/*	Now Inserting into Order Products By getting the PK from Orders Table	*/
					foreach(DataRow drOP in dsProd.Tables[0].Rows)
					{
						string sqlStrFinalInsert = "INSERT INTO Order_Products(Order_ID,Product_ID,Quantity, Price, Quantity_Price) Values(";
						sqlStrFinalInsert += Convert.ToInt32(maxOrderID) + ",";
						sqlStrFinalInsert += Convert.ToInt32(drOP[0].ToString()) + ",";
						sqlStrFinalInsert += Convert.ToInt32(drOP[2].ToString())+",";
						sqlStrFinalInsert += Convert.ToInt32(drOP[3].ToString())+",";
						sqlStrFinalInsert += Convert.ToInt32(drOP[4].ToString()) + ")";
						
						//MessageBox.Show(sqlStrFinalInsert);
						OleDbCommand myCmdInsert = new OleDbCommand( sqlStrFinalInsert , myCon ) ;
						try
						{
							//myCon.Open();
							//Execute the statement
							myCmdInsert.ExecuteNonQuery();
						}
						catch(Exception e)
						{
							MessageBox.Show("Your request Cannot be Completed :- in final Insert "+e.ToString());
						}
					}
					done = maxOrderID;
					
				}
				catch( OleDbException a)
				{
					MessageBox.Show("Error in inserting! \n" + a.ToString(), "Error");
				}
				finally
				{
					myCmd.Dispose();
					myCon.Close() ;
					//myCon.Dispose();
				}
			}
			else
			{
				MessageBox.Show("All fields must be completed.", "Error");
			}
			return done;
		}
		#endregion
		
		#region Delete()
		public bool Delete(int id)
		{
			bool done = false;
			
			//the string to get values from the textboxes and form an "INSERT INTO"
			// statement.
			string sqlStr = "DELETE FROM Products ";
			sqlStr += "WHERE Product_ID = " + Convert.ToString(id);
			
			OleDbCommand myCmd = new OleDbCommand( sqlStr , myCon ) ;
			try
			{
				myCon.Open();
				//Execute the statement
				myCmd.ExecuteNonQuery();
				//statusBar.Text="Connected - Now you can add records";
				done = true;
				
			}
			catch( OleDbException a)
			{
				MessageBox.Show("Error in deleting! \n" + a.ToString(), "Error");
			}
			finally
			{
				myCmd.Dispose();
				myCon.Close() ;
				//myCon.Dispose();
			}
			
			return done;
		}
		#endregion
		
		
	}
}
